package cmcc.dbi.execJdbcUtil;

import cmcc.dbi.dto.DbQueryDTO;
import cmcc.dbi.entity.InspectionItemRecord;
import cmcc.dbi.execEnum.DbTypeOfDbNameEnum;
import cmcc.dbi.execEnum.DbTypeOfDriverEnum;
import cmcc.dbi.execEnum.DbTypeOfUrlEnum;
import cmcc.dbi.execUtil.AESEncryptionUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class OceanBaseJdbcUtil implements JdbcUtil{
    @Override
    public JSONArray getTargetResult(List<InspectionItemRecord> scriptsLists, DbQueryDTO dbQueryDTO){
        Connection con;
        Statement stmt;

        //根据数据库类别从枚举类中活得对应的默认库和url
        String theDatName = DbTypeOfDbNameEnum.match(dbQueryDTO.getDbType()).getDbName();
        // 替换默认的url构建连接
        String url = DbTypeOfUrlEnum.match(dbQueryDTO.getDbType()).getDbUrl().replace("DBIP", dbQueryDTO.getDbIP())
                .replace("DBPORT", dbQueryDTO.getDbPort()).replace("DBNAME",theDatName);

        //String url = "jdbc:oceanbase://" + dbQueryDTO.getDbIP() + ":" + dbQueryDTO.getDbPort() + "/oceanbase?useSSL=false&useUnicode=true&characterEncoding=utf-8";

        //用于保存所有巡检项结果
        JSONArray queryAllResult = new JSONArray();
        try{
            //加载驱动
            String dbDriver = DbTypeOfDriverEnum.match(dbQueryDTO.getDbType()).getDbDriver();
            Class.forName(dbDriver);
            String pass = AESEncryptionUtil.decrypt(dbQueryDTO.getDbPass());
            log.info("连接信息: "+dbQueryDTO.getDbIP() + " - " + dbQueryDTO.getDbPort()+" - "+dbQueryDTO.getDbUser()+
                    " - "+dbQueryDTO.getDbPass());
            con = DriverManager.getConnection(url, dbQueryDTO.getDbUser(), pass);
            if (con != null) {
                log.info(dbQueryDTO.getDbIP() + "的" + dbQueryDTO.getDbType() + "数据库连接成功");
                // 用于执行静态SQL语句并返回其产生的结果的对象
                stmt = con.createStatement();

                //分别执行各个脚本
                for (InspectionItemRecord scriptsList : scriptsLists) {
                    //保存单个巡检项对应的结果
                    JSONObject querySingleResult = new JSONObject();
                    List<Map<String, Object>> lisTmp = new ArrayList<>();
                    //获取当前巡检项的脚本
                    String SqlCom = scriptsList.getScript();
                    //执行某个脚本
                    try{
                        boolean hasResult = stmt.execute(SqlCom);
                        if (hasResult){
                            //有执行结果
                            log.info("The SqlCom execute sucess");
                            ResultSet resultSet = stmt.getResultSet();
                            //多个key-value 代表 一列多行 ，实际只有一行
                            Map<String, Object> tmp = new HashMap<>();
                            while (resultSet.next()){
                                //获取第一列表头
                                String columnName =  resultSet.getMetaData().getColumnLabel(1);
                                //获取结果
                                String values = resultSet.getString(columnName);
                                //多行--正常只有一行
                                tmp.put(columnName,values);
                                log.info("执行结果：" + columnName + " -- "+values );
                            }
                            //某一巡检项对应的结果，一列多行（应是一列一行） [{key-value},{key-value}]
                            lisTmp.add(tmp);
                        }else {
                            log.info("The SqlCom ececute sucess but no results");
                        }
                    }catch (SQLException e) {
                        log.info("The SqlCom ececute failed");
                        //记录错误信息
                        Map<String, Object> tmp = new HashMap<>();
                        tmp.put("error_info",String.valueOf(e));
                        log.info("error_info："+ e );
                        lisTmp.add(tmp);
                    }
                    querySingleResult.put("item_id", scriptsList.getItemId());
                    querySingleResult.put("jdbc_result", lisTmp);
                    queryAllResult.add(querySingleResult);
                }
                stmt.close();
                con.close();
            }
        } catch (ClassNotFoundException | SQLException e) {
            log.error(dbQueryDTO.getDbIP() + "的" + dbQueryDTO.getDbType() + "数据库执行巡检脚本失败 " + e.getMessage());
        }
        return queryAllResult;
    }

    //测试
    public static void main(String[] args) {

        List<InspectionItemRecord> sqls = new ArrayList<>();

        InspectionItemRecord l1 = new InspectionItemRecord();
        l1.setItemRecordId(1L);
        l1.setScript("select count(status) from oceanbase.__all_server where status!='active';");
        l1.setItemScriptName("A");
        l1.setItemId(1L);
        l1.setItemName("A");
        l1.setTaskRecordId(1L);
        l1.setTaskId(1L);
        sqls.add(l1);

        InspectionItemRecord l2 = new InspectionItemRecord();
        l2.setItemRecordId(2L);
        l2.setScript("select count(*) from oceanbase.__all_unit;");
        l2.setItemScriptName("B");
        l2.setItemId(2L);
        l2.setItemName("B");
        l2.setTaskRecordId(1L);
        l2.setTaskId(1L);
        sqls.add(l2);

        DbQueryDTO dbQueryDTO = new DbQueryDTO("OceanBase", "192.168.248.128","2881","root","123456","oceanbase");

        JSONArray targetResult = new OceanBaseJdbcUtil().getTargetResult(sqls, dbQueryDTO);

        System.out.println(targetResult);
    }

}
